﻿using System.Data;
using System.Linq.Expressions;
using Model.CommonModel.PageModel;
using SqlSugar;
using SqlSugar.IOC;

namespace Repository
{
    /// <summary>
    /// SqlSugar仓储实现
    /// </summary>
    public class SqlSugarBaseRepository<T> : IBaseRepository<T> where T : class, new()
    {
      
        public ISqlSugarClient _db;
        public SqlSugarBaseRepository()
        {
            /* //静态单例模式。还可以采用IServiceProvider依赖注入，
             //依赖注入松耦合，生命周期管理可控，多库操作简单，适用于中大型项目，多库（DI 动态注入），微服务
             //DbScoped.SugarScope（静态单例）紧耦合 、生命周期管理不可控、多库支持	难（需手动切换）
             // 配置AOP监听*/
            _db = DbScoped.SugarScope;
            _db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine($"SQL: {sql}");
                Console.WriteLine($"Parameters: {string.Join(",", pars.Select(p => $"{p.ParameterName}={p.Value}"))}");
            };
        }

        #region 增删改查基础操作

        public async Task<int> InsertAsync(T entity)
        {
            return await _db.Insertable(entity).ExecuteCommandAsync();
        }

        public async Task<int> InsertRangeAsync(List<T> entities)
        {
            return await _db.Insertable(entities).ExecuteCommandAsync();
        }

        public async Task<T> GetByIdAsync(object id)
        {
            return await _db.Queryable<T>().In(id).SingleAsync();
        }

        public async Task<T> GetFirstAsync(Expression<Func<T, bool>> whereExpression)
        {
            return await _db.Queryable<T>().Where(whereExpression).FirstAsync();
        }

        public async Task<bool> UpdateAsync(T entity)
        {
            return await _db.Updateable(entity).ExecuteCommandHasChangeAsync();
        }

        public async Task<bool> UpdateAsync(Expression<Func<T, bool>> whereExpression, Expression<Func<T, T>> updateExpression)
        {
            return await _db.Updateable<T>()
                .SetColumns(updateExpression)
                .Where(whereExpression)
                .ExecuteCommandHasChangeAsync();
        }

        public async Task<bool> DeleteAsync(object id)
        {
            return await _db.Deleteable<T>(id).ExecuteCommandHasChangeAsync();
        }

        public async Task<int> DeleteAsync(Expression<Func<T, bool>> whereExpression)
        {
            return await _db.Deleteable<T>().Where(whereExpression).ExecuteCommandAsync();
        }

        #endregion

        #region add

        /// <summary>
        /// 插入指定列使用
        /// </summary>
        /// <param name="parm"></param>
        /// <param name="iClumns"></param>
        /// <param name="ignoreNull"></param>
        /// <returns></returns>
        public int Add(T parm, Expression<Func<T, object>> iClumns = null, bool ignoreNull = true)
        {
            return _db.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand();
        }

        /// <summary>
        /// 插入实体
        /// </summary>
        /// <param name="t"></param>
        /// <param name="IgnoreNullColumn">默认忽略null列</param>
        /// <returns></returns>
        public int Add(T t)
        {
            return _db.Insertable(t).ExecuteCommand();
        }

        /// <summary>
        /// 插入实体，可忽略指定列
        /// </summary>
        /// <param name="parm">实体</param>
        /// <param name="ignoreClumns">忽略指定列</param>
        /// <returns>插入之后的实体信息</returns>
        public T AddIgnoreClum(T parm, Expression<Func<T, object>> ignoreClumns)
        {
            return _db.Insertable(parm).IgnoreColumns(ignoreClumns).ExecuteReturnEntity();
        }

        //public int InsertIgnoreNullColumn(T t)
        //{
        //    return _db.Insertable(t).IgnoreColumns(true).ExecuteCommand();
        //}

        //public int InsertIgnoreNullColumn(T t, params string[] columns)
        //{
        //    return _db.Insertable(t).IgnoreColumns(columns).ExecuteCommand();
        //}

        //public int Insert(SqlSugarClient client, T t)
        //{
        //    return client.Insertable(t).ExecuteCommand();
        //}

        //public long InsertBigIdentity(T t)
        //{
        //    return base._db.Insertable(t).ExecuteReturnBigIdentity();
        //}

        public int Insert(List<T> t)
        {
            return _db.Insertable(t).ExecuteCommand();
        }

        public long InsertReturnBigIdentity(T t)
        {
            return _db.Insertable(t).ExecuteReturnBigIdentity();
        }

        //public int InsertIgnoreNullColumn(List<T> t)
        //{
        //    return base._db.Insertable(t).IgnoreColumns(true).ExecuteCommand();
        //}

        //public int InsertIgnoreNullColumn(List<T> t, params string[] columns)
        //{
        //    return base._db.Insertable(t).IgnoreColumns(columns).ExecuteCommand();
        //}
        //public int Insert(T parm, Expression<Func<T, object>> iClumns = null, bool ignoreNull = true)
        //{
        //    return base._db.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand();
        //}
        //public DbResult<bool> InsertTran(T t)
        //{
        //    var result = base._db.Ado.UseTran(() =>
        //    {
        //        base._db.Insertable(t).ExecuteCommand();
        //    });
        //    return result;
        //}

        //public DbResult<bool> InsertTran(List<T> t)
        //{
        //    var result = base._db.Ado.UseTran(() =>
        //    {
        //        base._db.Insertable(t).ExecuteCommand();
        //    });
        //    return result;
        //}

        //public T InsertReturnEntity(T t)
        //{
        //    return base._db.Insertable(t).ExecuteReturnEntity();
        //}

        //public T InsertReturnEntity(T t, string sqlWith = SqlWith.UpdLock)
        //{
        //    return base._db.Insertable(t).With(sqlWith).ExecuteReturnEntity();
        //}

        //public bool ExecuteCommand(string sql, object parameters)
        //{
        //    return base._db.Ado.ExecuteCommand(sql, parameters) > 0;
        //}

        //public bool ExecuteCommand(string sql, params SugarParameter[] parameters)
        //{
        //    return base._db.Ado.ExecuteCommand(sql, parameters) > 0;
        //}

        //public bool ExecuteCommand(string sql, List<SugarParameter> parameters)
        //{
        //    return base._db.Ado.ExecuteCommand(sql, parameters) > 0;
        //}

        #endregion add

        #region update

        //public bool UpdateEntity(T entity, bool ignoreNullColumns = false)
        //{
        //    return base._db.Updateable(entity).IgnoreColumns(ignoreNullColumns).ExecuteCommand() > 0;
        //}

        //public bool Update(T entity, Expression<Func<T, bool>> expression)
        //{
        //    return base._db.Updateable(entity).Where(expression).ExecuteCommand() > 0;
        //}

        //public bool Update(T entity, Expression<Func<T, object>> expression, bool ignoreAllNull = false)
        //{
        //    return base._db.Updateable(entity).UpdateColumns(expression).IgnoreColumns(ignoreAllNull).ExecuteCommand() > 0;
        //}

        //public bool Update(T entity, Expression<Func<T, object>> expression, Expression<Func<T, bool>> where)
        //{
        //    return base._db.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand() > 0;
        //}

        //public bool Update(SqlSugarClient client, T entity, Expression<Func<T, object>> expression, Expression<Func<T, bool>> where)
        //{
        //    return client.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand() > 0;
        //}

        /// <summary>
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="list"></param>
        /// <param name="isNull">默认为true</param>
        /// <returns></returns>
        public bool Update(T entity, List<string> list = null, bool isNull = true)
        {
            if (list == null)
            {
                list = new List<string>()
            {
                "Create_By",
                "Create_time"
            };
            }
            //base._db.Updateable(entity).IgnoreColumns(c => list.Contains(c)).Where(isNull).ExecuteCommand()
            return _db.Updateable(entity).IgnoreColumns(isNull).IgnoreColumns(list.ToArray()).ExecuteCommand() > 0;
        }

        public bool Update(List<T> entity)
        {
            return _db.Updateable(entity).ExecuteCommand() > 0;
        }

        public bool Update(Expression<Func<T, bool>> where, Expression<Func<T, T>> columns)
        {
            return _db.Updateable<T>().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommand() > 0;
        }

        #endregion update

        public DbResult<bool> UseTran(Action action)
        {
            var result = _db.Ado.UseTran(() => action());
            return result;
        }

        public DbResult<bool> UseTran(SqlSugarClient client, Action action)
        {
            var result = client.Ado.UseTran(() => action());
            return result;
        }

        public bool UseTran2(Action action)
        {
            var result = _db.Ado.UseTran(() => action());
            return result.IsSuccess;
        }

        #region delete

        /// <summary>
        /// 删除表达式
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public int Delete(Expression<Func<T, bool>> expression)
        {
            return _db.Deleteable<T>().Where(expression).ExecuteCommand();
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Delete(object[] obj)
        {
            return _db.Deleteable<T>().In(obj).ExecuteCommand();
        }

        public int Delete(object id)
        {
            return _db.Deleteable<T>(id).ExecuteCommand();
        }

        public bool DeleteTable()
        {
            return _db.Deleteable<T>().ExecuteCommand() > 0;
        }

        public bool DeleteAll(List<T> ts)
        {
            return _db.Deleteable<List<T>>().ExecuteCommand() > 0;
        }

        #endregion delete

        #region query

        public bool Any(Expression<Func<T, bool>> expression)
        {
            return _db.Queryable<T>().Where(expression).Any();
        }

        public ISugarQueryable<T> Queryable()
        {
            return _db.Queryable<T>();
        }

        //public ISugarQueryable<ExpandoObject> Queryable(string tableName, string shortName)
        //{
        //    return _db.Queryable(tableName, shortName);
        //}

        public List<T> GetList(Expression<Func<T, bool>> expression)
        {
            return _db.Queryable<T>().Where(expression).ToList();
        }

      
        /// <summary> 获得一条数据 </summary> <param name="where">Expression<Func<T, bool>></param> <returns></returns>
        public T GetFirst(Expression<Func<T, bool>> where)
        {
            return _db.Queryable<T>().Where(where).First();
        }

        /// <summary>
        /// 根据主值查询单条数据
        /// </summary>
        /// <param name="pkValue">主键值</param>
        /// <returns>泛型实体</returns>
        public T GetId(object pkValue)
        {
            return _db.Queryable<T>().InSingle(pkValue);
        }

        /// <summary>
        /// 根据条件查询分页数据
        /// </summary>
        /// <param name="where"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm)
        {
            var source = _db.Queryable<T>().Where(where);

            return source.ToPage(parm);
        }

        public PagedInfo<T> GetPages(Expression<Func<T, bool>> where, PagerInfo parm, Expression<Func<T, object>> order, string orderEnum = "Asc")
        {
            var source = _db.Queryable<T>().Where(where).OrderByIF(orderEnum == "Asc", order, OrderByType.Asc).OrderByIF(orderEnum == "Desc", order, OrderByType.Desc);

            return source.ToPage(parm);
        }

        /// <summary>
        /// 查询所有数据(无分页,请慎用)
        /// </summary>
        /// <returns></returns>
        public List<T> GetAll(bool useCache = false, int cacheSecond = 3600)
        {
            return _db.Queryable<T>().WithCacheIF(useCache, cacheSecond).ToList();
        }

        public List<T> GetByIdList(object[] obj)
        {
            return _db.Queryable<T>().In(obj).ToList();
        }

        public int Count(Expression<Func<T, bool>> where)
        {
            return _db.Queryable<T>().Count(where);
        }

        #endregion query

        /// <summary> 此方法不带output返回值 var list = new List<SugarParameter>(); list.Add(new
        /// SugarParameter(ParaName, ParaValue)); input </summary> <param
        /// name="procedureName"></param> <param name="parameters"></param> <returns></returns>
        public DataTable UseStoredProcedureToDataTable(string procedureName, List<SugarParameter> parameters)
        {
            return _db.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters);
        }

        /// <summary> 带output返回值 var list = new List<SugarParameter>(); list.Add(new
        /// SugarParameter(ParaName, ParaValue, true)); output list.Add(new SugarParameter(ParaName,
        /// ParaValue)); input </summary> <param name="procedureName"></param> <param
        /// name="parameters"></param> <returns></returns>
        public (DataTable, List<SugarParameter>) UseStoredProcedureToTuple(string procedureName, List<SugarParameter> parameters)
        {
            var result = (_db.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters), parameters);
            return result;
        }

        #region 查询操作

        public async Task<List<T>> GetAllAsync(bool useCache = false, int cacheSeconds = 3600)
        {
            return await _db.Queryable<T>()
                .WithCacheIF(useCache, cacheSeconds)
                .ToListAsync();
        }

        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> whereExpression)
        {
            return await _db.Queryable<T>()
                .Where(whereExpression)
                .ToListAsync();
        }

        public async Task<PagedInfo<T>> GetPagedListAsync(
            Expression<Func<T, bool>> whereExpression,
            int pageIndex = 1,
            int pageSize = 20,
            Expression<Func<T, object>> orderByExpression = null,
            OrderByType orderByType = OrderByType.Asc)
        {
            RefAsync<int> totalCount = 0;
            var query = _db.Queryable<T>().Where(whereExpression);

            if (orderByExpression != null)
            {
                query = orderByType == OrderByType.Asc
                    ? query.OrderBy(orderByExpression)
                    : query.OrderByDescending(orderByExpression);
            }

            var list = await query.ToPageListAsync(pageIndex, pageSize, totalCount);

            return new PagedInfo<T>
            {
                PageIndex = pageIndex,
                PageSize = pageSize,
                TotalCount = totalCount,
                Data = list
            };
        }

        public async Task<bool> AnyAsync(Expression<Func<T, bool>> whereExpression)
        {
            return await _db.Queryable<T>().AnyAsync(whereExpression);
        }

        public async Task<int> CountAsync(Expression<Func<T, bool>> whereExpression)
        {
            return await _db.Queryable<T>().CountAsync(whereExpression);
        }

        #endregion

        #region 事务与存储过程

        public async Task<bool> UseTransactionAsync(Func<Task> action)
        {
            try
            {
                await _db.Ado.UseTranAsync(async () => await action());
                return true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<DataTable> ExecuteStoredProcedureAsync(string procedureName, List<SugarParameter> parameters)
        {
            return await _db.Ado.UseStoredProcedure().GetDataTableAsync(procedureName, parameters);
        }
        public async Task<(DataTable Result, List<SugarParameter> OutParameters)> ExecuteStoredProcedureWithOutputAsync(
            string procedureName,
            List<SugarParameter> parameters)
        {
            var result = await _db.Ado.UseStoredProcedure().GetDataTableAsync(procedureName, parameters);

            // 筛选出输出参数（ParameterDirection.Output 或 ParameterDirection.InputOutput）
            var outParams = parameters
                .Where(p => p.Direction == ParameterDirection.Output ||
                           p.Direction == ParameterDirection.InputOutput)
                .ToList();

            return (result, outParams);
        }

        public bool Update(T entity, Expression<Func<T, bool>> expression)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
    public static class QueryableExtension
    {
        /// <summary>
        /// 读取列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static PagedInfo<T> ToPage<T>(this ISugarQueryable<T> source, PagerInfo parm)
        {
            var page = new PagedInfo<T>();
            var total = source.Count();
            page.TotalCount = total;
            page.PageSize = parm.PageSize;
            page.PageIndex = parm.PageNum;

            //page.DataSource = source.OrderByIF(!string.IsNullOrEmpty(parm.Sort), $"{parm.OrderBy} {(parm.Sort == "descending" ? "desc" : "asc")}").ToPageList(parm.PageNum, parm.PageSize);
            page.Data = source.ToPageList(parm.PageNum, parm.PageSize);
            return page;
        }
    }
}

